const mysql = require('mysql2/promise');

async function main() {
    const db = {
        host: process.env.DB_HOST || '43.143.253.188',
        port: parseInt(process.env.DB_PORT || '3306', 10),
        user: process.env.DB_USER || 'nextjs',
        password: process.env.DB_PASS || 'yEAiRE2jcxTyRBy5',
        database: process.env.DB_NAME || 'nextjs',
        timezone: 'Z',
    };

    const known = {
        83: { name: '副将经验书', pic: 'fj_exp_book.png' },
        147: { name: '初级副将心法', pic: 'xinfa_lv1.png' },
        148: { name: '高级副将心法', pic: 'xinfa_lv2.png' },
        149: { name: '特级副将心法', pic: 'xinfa_lv3.png' },
        425: { name: '密宗副将心法', pic: 'xinfa_lv4.png' },
        304: { name: '常人心法·三级', pic: 'xinfa_common_lv3.png' },
    };

    let conn;
    try {
        conn = await mysql.createConnection(db);
        console.log('✅ 连接数据库成功');

        // 1) 补齐 basic_items 的 name/pic（仅在为空时）
        let updatedBasic = 0;
        for (const [idStr, meta] of Object.entries(known)) {
            const id = Number(idStr);
            const [rows] = await conn.execute('SELECT id, name, pic FROM basic_items WHERE id = ?', [id]);
            if (!rows || rows.length === 0) continue;
            const row = rows[0];
            const needName = !row.name || row.name.trim() === '';
            const needPic = !row.pic || row.pic.trim() === '';
            if (needName || needPic) {
                const newName = needName ? meta.name : row.name;
                const newPic = needPic ? meta.pic : row.pic;
                await conn.execute(
                    'UPDATE basic_items SET name = ?, pic = ?, updated_at = NOW() WHERE id = ?',
                    [newName, newPic, id]
                );
                updatedBasic++;
            }
        }
        console.log(`📦 basic_items 补齐条数: ${updatedBasic}`);

        // 2) 批量回填 character_inventory 的 name/pic（仅在为空时）
        const [result] = await conn.execute(`
      UPDATE character_inventory ci
      JOIN basic_items bi ON bi.id = ci.itemId
      SET 
        ci.name = CASE WHEN (ci.name IS NULL OR ci.name = '') THEN bi.name ELSE ci.name END,
        ci.pic  = CASE WHEN (ci.pic  IS NULL OR ci.pic  = '') THEN bi.pic  ELSE ci.pic  END,
        ci.updated_at = NOW()
      WHERE ci.status = 1 AND (
        ci.name IS NULL OR ci.name = '' OR ci.pic IS NULL OR ci.pic = ''
      )
    `);
        // result.affectedRows 在 mysql2 中可用
        console.log(`🛠 回填 character_inventory 条数: ${result.affectedRows || 0}`);

        console.log('✅ 回填完成');
    } catch (e) {
        console.error('❌ 执行失败:', e.message);
        process.exit(1);
    } finally {
        if (conn) await conn.end();
    }
}

main();
